package com.rybbaby.tss.front.tssStatistics.dao.imp;

import com.rybbaby.tss.core.bean.PageBean;
import com.rybbaby.tss.core.utils.BeanUtils;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.front.tssStatistics.dao.IViewConsultancyStatisticsDao;
import com.rybbaby.tss.front.tssStatistics.entity.ViewConsultancyStatistics;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description 员工表数据库操作实现
 * 
 * @author 雷春琼
 * 
 * @createDate 2017-08-10 上午12:25:24
 */
@Repository
public class ViewConsultancyStatisticsDaoImp extends GenericDao<ViewConsultancyStatistics> implements IViewConsultancyStatisticsDao {

    /**
     * 根据状态来修改课时包信息
     *
     * @param targetDateId
     * @param isBlockUp
     * @return
     */
    @Override
    public Integer updateStaffByIsBlockUp(String targetDateId, Boolean isBlockUp) {
        return this.executeSql("update sys_admin_user set updateTime=Now(),isBlockUp=? where id=?",new Object[]{isBlockUp,targetDateId});
    }

    @Override
    public PageBean pageList(HashMap<String, Object> paramHashMap, int pageNo, int pageSize) throws InstantiationException, IllegalAccessException {
        String id = (String)paramHashMap.get("id");
        String adminUserId = (String)paramHashMap.get("adminUserId");
        String packageCreateTime_begin = (String)paramHashMap.get("packageCreateTime_begin");
        String packageCreateTime_end = (String)paramHashMap.get("packageCreateTime_end");

        String idSql = "";
        String adminUserIdSql = "";
        String allocationCreateTime_beginSql = "";
        String allocationCreateTime_endSql = "";
        String packageCreateTime_beginSql = "";
        String packageCreateTime_endSql = "";
        String refundCreateTime_begin = "";
        String refundCreateTime_end = "";
        if(id!=null && !id.equals("")){
            idSql = "\tAND sys_admin_user.id = '"+id+"'\n";
        }
        if(adminUserId!=null && !adminUserId.equals("")){
            idSql = "\tAND sys_admin_user.id in ('" + adminUserId + "') \n";
        }
        if(packageCreateTime_begin!=null && !packageCreateTime_begin.equals("")){
            allocationCreateTime_beginSql = "\tAND unix_timestamp(tss_allocation.createTime) >= unix_timestamp('"+packageCreateTime_begin+"')\n";
            packageCreateTime_beginSql = "\tAND unix_timestamp(tss_member_package.createTime) >= unix_timestamp('"+packageCreateTime_begin+"')\n";
            refundCreateTime_begin = "\tAND unix_timestamp(tss_member_return_premium.updateTime) >= unix_timestamp('"+packageCreateTime_begin+"')\n";
        }
        if(packageCreateTime_end!=null && !packageCreateTime_end.equals("")){
            allocationCreateTime_endSql = "\tAND unix_timestamp(tss_allocation.createTime) <= unix_timestamp('"+packageCreateTime_end+"')\n";
            packageCreateTime_endSql = "\tAND unix_timestamp(tss_member_package.createTime) <= unix_timestamp('"+packageCreateTime_end+"')\n";
            refundCreateTime_end = "\tAND unix_timestamp(tss_member_return_premium.updateTime) <= unix_timestamp('"+packageCreateTime_end+"')\n";
        }

        String sql = "SELECT\n" +
                "\tA.id AS id,\n" +
                "\tA.realName AS realName,\n" +
                "\tA.distributionNum AS distributionNum,\n" +
                "\tcount(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN C.applyStatus = 0 THEN\n" +
                "\t\t\tC.id\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                "\t) AS addedNumber,\n" +
                "\tcount(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN C.applyStatus = 1 THEN\n" +
                "\t\t\tC.id\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                "\t) AS renewalNumber,\n" +
                "\tsum(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN C.applyStatus = 0 THEN\n" +
                "\t\t\tC.sumPrice\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                "\t) AS sumAdded,\n" +
                "\tsum(\n" +
                "\t\tCASE\n" +
                "\t\tWHEN C.applyStatus = 1 THEN\n" +
                "\t\t\tC.sumPrice\n" +
                "\t\tELSE\n" +
                "\t\t\t0\n" +
                "\t\tEND\n" +
                "\t) AS sumRenewal,\n" +
                "\tcount(D.memberId) AS refundNumber,\n" +
                "\tIFNULL(sum(D.amout),0) AS sumRefund\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tsys_admin_user.id AS id,\n" +
                "\t\t\tsys_admin_user.`realName` AS realName,\n" +
                "\t\t\tIFNULL(count(B.studentId),0) AS distributionNum\n" +
                "\t\tFROM\n" +
                "\t\t\tsys_admin_user\n" +
                "\t\tLEFT JOIN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\t*\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\ttss_allocation\n" +
                "\t\t\tWHERE 1=1\n" +
                allocationCreateTime_beginSql +
                allocationCreateTime_endSql +
                "\t\t)B ON B.adminUserId = sys_admin_user.id\n" +
                "\t\tWHERE\n" +
                "\t\t\tsys_admin_user.post = '4'\n" +
                "\t\t\tFIND_IN_SET( 4, sys_admin_user.partTime) \n" +
                adminUserIdSql +
                idSql +
                "\t\tGROUP BY\n" +
                "\t\t\tsys_admin_user.id\n" +
                "\t) A\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\t*\n" +
                "\tFROM\n" +
                "\t\ttss_member_package\n" +
                "\tWHERE 1=1\n" +
                packageCreateTime_beginSql +
                packageCreateTime_endSql +
                ")C ON C.adminUserId = A.id\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\t*\n" +
                "\tFROM\n" +
                "\t\ttss_member_return_premium\n" +
                "\tWHERE\n" +
                "\t\ttss_member_return_premium.refundType = 1\n" +
                "\tAND tss_member_return_premium.isDelete = 0\n" +
                "\tAND tss_member_return_premium.refundStatus = 5\n" +
                refundCreateTime_begin +
                refundCreateTime_end +
                ") D ON C.id = D.packageId\n" +
                "GROUP BY\n" +
                "\tA.id";
        System.out.println(sql);
        PageBean pageBean = this.pageList(sql, pageNo, pageSize);
        List<ViewConsultancyStatistics> populate = BeanUtils.populate(ViewConsultancyStatistics.class, (List<Map<String, Object>>) pageBean.getList(), "sumPerformance", "attendanceRate");
        pageBean.setList(populate);
        return pageBean;
    }
}
